from flask import Flask, render_template, request, flash,  jsonify, redirect, url_for, session
from utils import query, map_student_course, recommed_module
import json
import time
import os
# 创建flask对象
app = Flask(__name__)
app.config['SECRET_KEY'] = 'gsolvit'

@app.route('/personal_information', methods=['GET', 'POST'])
def personal_information():
    """
    功能(个人中心界面): 跳转到个人中心页面
    author: zhangyunwei
    """
    return render_template('personal_information.html')

@app.route('/profile', methods=['GET'])
def profile():
    """
    获取当前登录用户的个人信息
    author: zhangyunwei
    """
    stu_id = session.get('stu_id')
    sql = "select * from STUDENT t where t.stu_no='%s'" % (stu_id)
    result = query.query(sql)
    data = {
        "姓名": result[0][0],
        "学号": result[0][2],
        "学院": result[0][3],
        "专业": result[0][4]
    }
    return jsonify(data)

#
@app.route('/stu_profile', methods=['GET'])
def stuProfile():
    """
    获取指定用户的信息
    author: zhangyunwei
    """
    userId = request.args.get("userId")
    sql = "select * from STUDENT t where t.stu_no='%s'" % (userId)
    result = query.query(sql)
    data = {
        "姓名": result[0][0],
        "学号": result[0][2],
        "学院": result[0][3],
        "专业": result[0][4]
    }
    return jsonify(data)

@app.route('/elective_record', methods=['GET'])
def elective_record():
    """
        展示选课
        author: zhangyunwei
    """
    stu_id = session.get('stu_id')
    sql = " SELECT t1.id, t2.NAME,t2.info FROM stu_cou t1 LEFT JOIN course t2 ON t1.cuo_no = t2.no WHERE t1.stu_no = '%s'" % (stu_id)

    result = query.query(sql)
    list1 = [] # 列表
    for data in result:
        jsonobj = { # 字典数据类型
            "id": data[0],
            "name": data[1],
            "info": data[2],
        }
        list1.append(jsonobj)
    #return jsonify(list1)
    return render_template('stu_cou.html', result=list1)


@app.route('/elective_record', methods=['delete'])
def elective_record_delete():
    """
        取消选课
        author: zhangyunwei
    """
    id = request.args.get("id")
    sql = " delete from stu_cou where id = '%s'" % (id)
    result = query.update(sql)
    if result == 1:
        result ={
            "result": "success"
        }
    elif result == 0:
        result ={
            "result": "error"
        }
    return result

@app.route('/bbs_add_topic', methods=['POST'])
def add_topic():
    """
    论坛：添加帖子主题
    author: zhangyunwei
    """
    stu_id = session.get('stu_id')
    sql = "select NAME from STUDENT where STU_NO = '%s'" % stu_id
    author= query.query(sql)
    author = author[0][0]
    request_body = request.json
    now = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
    topic = request_body["topic"]
    info = request_body["info"]
    sql = "INSERT INTO topic (title,info,time,author) VALUES ('%s', '%s', '%s', '%s')" % (topic,info,now,author)
    query.update(sql)
    result = {
        "result": "success"
    }
    return result


@app.route('/bbs_topics', methods=['GET'])
def show_topic():
    """
    展示全部主题
    author: zhangyunwei
    """
    sql = " SELECT * FROM topic order by time"
    result = query.query(sql)
    list1 = [] # 列表
    for data in result:
        jsonobj = { # 字典数据类型
            "id": data[0],
            "title": data[1],
            "info": data[3],
            "author": data[2],
            "time": data[4],
        }
        list1.append(jsonobj)
    return jsonify(list1)
    #return render_template('topic.html', result=list1)



@app.route('/bbs_topic_detail', methods=['GET'])
def show_topic_detail():
    """
    展示全部帖子详情
    author: zhangyunwei
    """
    topic_id = request.args.get("topic_id")
    sql= "select t2.name,t1.* from  topic t1 LEFT JOIN student t2 ON t2.stu_no = t1.author where t1.id = '%s'" % (topic_id)
    result1 = query.query(sql)
    topic = {
        "id": result1[0][1],
        "title": result1[0][2],
        "author": result1[0][0],
        "info": result1[0][4],
        "time": result1[0][5]
    }

    sql2 = "select t2.name,t1.* from  content t1 LEFT JOIN student t2 ON t2.stu_no = t1.author where t1.topic_id  = '%s' order by t1.time " % (topic_id)
    result2 = query.query(sql2)
    content = [] # 列表
    for data2 in result2:
        reply = get_reply(data2[1])
        jsonobj = { # 字典数据类型
            "id": data2[1],
            "info": data2[3],
            "author": data2[0],
            "time": data2[5],
            "reply": reply
        }
        content.append(jsonobj)

    result = {
        "topic": topic,
        "content": content
    }
    return result
    #return render_template('topic.html', result=list1)

def get_reply(content_id):
    reply_sql = "select t2.name,t1.* from  reply t1 LEFT JOIN student t2 ON t2.stu_no = t1.author where t1.content_id = '%s' order by t1.time" % (content_id)
    result3 = query.query(reply_sql)
    reply = []  # 列表
    for data3 in result3:
        jsonobj = {  # 字典数据类型
            "id": data3[1],
            "info": data3[3],
            "author": data3[0],
            "time": data3[4]
        }
        reply.append(jsonobj)
    return reply
##################################################################################################################################

@app.route('/courses', methods=['GET'])
def stuCourses():
    """
    初始化选课大厅，返回所有课程
    author: linjuncheng
    """
    stu_id = session.get('stu_id')
    sql = "SELECT t1.no, t1.name, t1.info, CASE WHEN t2.id IS NULL THEN 1 ELSE 0 END AS choose_flag FROM course t1 LEFT JOIN stu_cou t2 ON t1.no = t2.cuo_no AND t2.stu_no = '%s' order by t1.no" % (stu_id)
    result = query.query(sql)
    list1 = [] # 列表
    for data in result:
        jsonobj = { # 字典数据类型
            "no": data[0],
            "name": data[1],
            "info": data[2],
            "choose_flag": data[3],
        }
        list1.append(jsonobj)
    #return jsonify(list1)
    return render_template('courses.html', result=list1)

@app.route('/courses',methods=['POST'])
def stuCourses_add():
    """
    在stu_cou表中增加记录
    author: linjuncheng
    """
    id = request.args.get("cou_no")
    userId = session.get('stu_id')
    now = time.time()
    now = time.strftime('%Y-%m-%d', time.localtime(now))
    now = str(now)
    sql = "INSERT INTO stu_cou (`stu_no`,`cuo_no`,`time`) VALUES ('%s','%s','%s')" % (userId, id, now)
    result = query.update(sql)
    if result == 1:
        result = {
            "result": "success"
        }
    elif result == 0:
        result = {
            "result": "error"
        }
    return (result)


@app.route('/bbs_add_info',methods=['POST'])
def bbs_add_info():
    """
    添加跟帖
    author:linjuncheng
    """
    stu_id = session.get('stu_id')
    now = time.time()
    now = time.strftime('%Y-%m-%d', time.localtime(now))
    now = str(now)
    request_body = request.json
    topic_id = request_body["topic_id"]
    info = request_body["info"]
    sql1 = "INSERT INTO content (`topic_id`,`info`,`author`,`time`) VALUES ('%s','%s','%s','%s')" % (topic_id,info, stu_id, now)
    result = query.update(sql1)
    if result == 1:
        result = {
            "result": "success"
        }
    elif result == 0:
        result = {
            "result": "error"
        }
    return (result)

@app.route('/bbs_add_reply',methods=['POST'])
def bbs_add_reply():
    """
    我也说一句
    author:linjuncheng
    """
    stu_id = session.get('stu_id')
    now = time.time()
    now = time.strftime('%Y-%m-%d', time.localtime(now))
    now = str(now)
    request_body = request.json
    info = request_body["info"]
    content_id = request_body["content_id"]
    sql = "INSERT INTO reply (`content_id`,`info`,`time`,`author`) VALUES ('%s','%s','%s','%s')" % (content_id, info, now, stu_id)
    result = query.update(sql)
    if result == 1:
        result = {
            "result": "success"
        }
    elif result == 0:
        result = {
            "result": "error"
        }
    return (result)

##################################################################################################################################
@app.route('/index', methods=['GET', 'POST'])
def index():
    return render_template('index.html')


@app.route('/manager', methods=['GET', 'POST'])
def manager():
    sql = "select * from STUDENT"
    result = query.query(sql)
    return render_template('manager.html', result=result)


@app.route('/managerAdd', methods=['GET', 'POST'])
def managerAdd():
    stu_id = session.get('stu_id')
    #print(stu_id)
    if stu_id == 'admin':
        if request.method == 'GET':
            #print('1111')
            return  render_template('managerAdd.html')
        else:
            #print('222')
            name = request.form.get('name')
            sex = request.form.get('sex')
            stu_no = request.form.get('stu_no')
            college = request.form.get('college')
            major = request.form.get('major')
            ad_year = request.form.get('ad_year')
            password = request.form.get('password')
            sql="INSERT INTO STUDENT VALUES ('%s','%s','%s','%s','%s','%s','%s','%s')" % (name,sex,stu_no,college,major,ad_year,password,stu_no)
            #print(sql)
            query.update(sql)
            return redirect(url_for('manager'))
    else:
        return u'页面不存在'


@app.route('/managerDelete', methods=['GET', 'POST'])
def managerDelete():
    stu_id = session.get('stu_id')
    #print(stu_id)
    if stu_id == 'admin':
        if request.method == 'GET':
            #print('1111')
            return render_template('managerDelete.html')
        else:
            #print('222')
            stu_no = request.form.get('stu_no')
            sql="DELETE FROM STUDENT WHERE STU_NO='%s'" % stu_no
            #print(sql)
            query.update(sql)
            return redirect(url_for('manager'))
    else:
        return u'页面不存在'


@app.route('/managerEdit', methods=['GET', 'POST'])
def managerEdit():
    stu_id = session.get('stu_id')
    if stu_id == 'admin':
        if request.method == 'GET':
            return render_template('managerEdit.html')
        else:
            stu_no = request.form.get('stu_no')
            name = request.form.get('name')
            sex = request.form.get('sex')
            college = request.form.get('college')
            major = request.form.get('major')
            ad_year = request.form.get('ad_year')
            password = request.form.get('password')

            sql="select * from STUDENT WHERE STU_NO='%s'" % stu_no
            result=query.query(sql)
            if name=='':
                name=result[0][0]
            if sex=='':
                sex=result[0][1]
            if college=='':
                college=result[0][3]
            if major=='':
                major=result[0][4]
            if ad_year=='':
                ad_year=result[0][5]

            sql="UPDATE STUDENT SET NAME='%s',SEX='%s',COLLEGE='%s',MAJOR='%s',AD_YEAR='%s',PASSWORD='%s',ID='%s' WHERE STU_NO='%s'" % (name, sex, college, major, ad_year, password, stu_no, stu_no)
            #print(sql)
            query.update(sql)
            return redirect(url_for('manager'))
    else:
        return u'页面不存在'


@app.route('/course_discussion', methods=['GET', 'POST'])
def course_discussion():
    if request.method == 'GET':
        return render_template('course_discussion.html')
    else:
        topic = request.form.get('topic')
        print(topic)
        comments = request.form.get('comments')
        #commenter = request.form.get('commenter')
        # print(len(topic))
        # print('course_discussion')
        # print(topic, commenter, comments)
        stu_id = session.get('stu_id')
        sql = "select NAME from STUDENT where STU_NO = '%s'" % stu_id
        stu_name = query.query(sql)
        stu_name = stu_name[0][0]
        now = time.time()
        now = time.strftime('%Y-%m-%d', time.localtime(now))
        now = str(now)
        news_id = stu_name + now
        sql = "INSERT INTO NEWS(TOPIC, COMMENTS, COMMENTER, NEWS_ID, IS_FIRST) VALUES ('%s', '%s', '%s', '%s')" % (topic, comments, stu_name, news_id)
        print(sql)
        query.update(sql)
        return redirect(url_for('news_center'))


@app.route('/', methods=['GET', 'POST'])
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method=='GET':
        return render_template('login.html')
    else:
        stu_id = request.form.get('stu_id')
        password = request.form.get('password')
        sql = "select * from STUDENT where STU_NO = '%s'" % stu_id
        result = query.query(sql)
        print(result)
        if len(result) != 0:
            #print(result[0][6], password)
            if result[0][6] == password:
                session['stu_id'] = result[0][2]
                session.permanent=True
                if stu_id=='admin':
                    return redirect(url_for('manager'))
                else:
                    return redirect(url_for('index'))
            else:
                return u'账号或密码错误'
        else:
            return u'不存在这个用户'


@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method=='GET':
        return render_template('register.html')
    else:
        stu_id = request.form.get('stu_id')
        user = request.form.get('user')
        password = request.form.get('password')
        password1 = request.form.get('password1')
        print(stu_id, user, password, password1)

        if(password1 != password):
            return u'两次输入密码不同，请检查'
        else:
            sql = "select * from STUDENT where STU_NO = '%s'" % stu_id
            #print(sql)
            result = query.query(sql)
            #print(result)
            if len(result) == 0:
                return u'没有这个用户了'
            else:
                if result[0][6] == user:
                    sql = "UPDATE student SET PASSWORD='%s' WHERE STU_NO='%s'" % (password, stu_id)
                    query.update(sql)
                    return redirect(url_for('login'))
                else:
                    return u'密码错误'


@app.route('/news_center', methods=['GET', 'POST'])
def news_center():
    sql = "select * from NEWS WHERE IS_FIRST='0'"
    result = query.query(sql)
    print(result)
    return render_template('news_center.html', result=result)


@app.route('/detail/<question>', methods=['GET', 'POST'])
def detail(question):
    print(question)
    #question=str(question)
    if request.method=='GET':
        sql="SELECT TOPIC, COMMENTS, COMMENTER, CREATE_TIME FROM NEWS WHERE NEWS_ID='%s' AND IS_FIRST='0'" % question
        title=query.query(sql)
        #print(title)
        title=title[0]
        sql="SELECT * FROM NEWS WHERE IS_FIRST='%s'" % question
        result=query.query(sql)
        return render_template('detail.html', title=title, result=result)
    else:
        comments = request.form.get('comments')
        stu_id = session.get('stu_id')
        sql = "select NAME from STUDENT where STU_NO = '%s'" % stu_id
        stu_name = query.query(sql)
        stu_name = stu_name[0][0]
        now = time.time()
        now = time.strftime('%Y-%m-%d', time.localtime(now))
        now = str(now)
        news_id = stu_name + now
        sql = "INSERT INTO NEWS(TOPIC, COMMENTS, COMMENTER, NEWS_ID, IS_FIRST) VALUES ('回复', '%s', '%s', '%s', '%s')" % (comments, stu_name, news_id,question)
        print(sql)
        query.update(sql)

        sql = "SELECT TOPIC, COMMENTS, COMMENTER, CREATE_TIME FROM NEWS WHERE NEWS_ID='%s' AND IS_FIRST='0'" % question
        title = query.query(sql)
        # print(title)
        title = title[0]
        sql = "SELECT * FROM NEWS WHERE IS_FIRST='%s'" % question
        result = query.query(sql)
        return render_template('detail.html', title=title, result=result)


@app.route('/recommed', methods=['GET', 'POST'])
def recommed():
    return render_template('recommed.html')

@app.route("/getRecommedData", methods=['GET','POST'])
def getRecommedData():
    stu_no = session.get('stu_id')
    id2Student, id2Course, stuNo2MatId = map_student_course.get_map_student()
    scoreMatrix = map_student_course.get_matrix(id2Student)
    """
    函数，recommedCourse：使用SVD进行课程推荐：
    返回:(课程1ID， 课程1评分)
    """
    topNCourse, topNStudent = recommed_module.recommedCoursePerson(scoreMatrix, stuNo2MatId[stu_no], N=20)
    """
    将得到的Course与Person装换为前端图标需要的json格式:
     {
        "source": [
            [2.3, "计算机视觉"],
            [1.1, "自然语言处理"],
            [2.4, "高等数学"],
            [3.1, "线性代数"],
            [4.7, "计算机网络"],
            [5.1, "离散数学"]
        ]
     }   
    """

    id2Student = {i:id2Student[i][0] for i in id2Student.keys()}
    print(id2Student)
    print(id2Course)
    courseJson = recommed_module.toBarJson(topNCourse, id2Course)
    personJson = recommed_module.toBarJson(topNStudent, id2Student)
    courseJson = recommed_module.regularData(courseJson, 1, 5)
    personJson = recommed_module.regularData(personJson, 0, 1)

    coursePersonJson = {}
    coursePersonJson['course'] = courseJson
    coursePersonJson['person'] = personJson
    return jsonify(coursePersonJson)

@app.route('/train_plan', methods=['GET', 'POST'])
def train_plan():
    return render_template('train_plan.html')


@app.route('/get_info', methods=['GET', 'POST'])
def get_info():
    """
    功能(培养计划界面): 初始进入培养计划界面，根据stu_id从数据库中得到数据并将其转换为计划树所需json格式数据
    :return: planTree:(json) 计划树所需数据
    """
    stu_id = session.get('stu_id')
    planTree = query.getPlanTreeJson(stu_id)
    print(planTree)
    return jsonify(planTree)


@app.route('/submit_train_plan', methods=['GET', 'POST'])
def submit_train_place():
    """
    功能1：实现数据库学生选课信息的更新
    功能2: 实现计划树以及进度条的提交更新。
    :return:
    """
    """功能1："""
    twoData = request.get_json(force=True)
    train_plan = twoData['tree']
    scores = twoData['scores']

    #train_plan['name'] = "数据转换成功"
    print('反馈回来的数据是：')
    print(train_plan)
    data = train_plan['children']
    array_finish = [0]*120
    #print(array_finish)
    for data_children in data:
        data_children = data_children['children']
        #print(data_children)
        for data_children_child_1 in data_children:
            #print('data_children_child', data_children_child)
            data_children_child_1 = data_children_child_1['children']
            for data_children_child in data_children_child_1:
                name = data_children_child['children'][0]['name']
                color = data_children_child['children'][0]['itemStyle']['borderColor']
                #print(name, color)
                sql = "select CO_100 from education_plan WHERE CO_NAME='%s'" % name
                co_100 = query.query(sql)
                co_100 = co_100[0][0]

                if color == 'red':
                    array_finish[int(co_100)] = 0
                else:
                    array_finish[int(co_100)] = 1
    finish_co = ''
    for i in range(1, 119):
        if array_finish[i] == 1:
            finish_co += '1'
        else:
            finish_co += '0'
    print(finish_co)
    #print(array_finish)

    stu_id = session.get('stu_id')
    query.updateDatabase(stu_id, train_plan)
    query.updateScore(stu_id, scores)

    """功能2："""
    train_plan_str = json.dumps(train_plan)
    train_plan_str = train_plan_str.replace("yellow", "green")
    train_plan = json.loads(train_plan_str)
    return jsonify(train_plan)


if __name__ == '__main__':
    app.run("127.0.0.1", debug=True)

